package com.eascs.p2p.admin.manager.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.p2p.admin.manager.IInvestRecordManager;
import com.eascs.p2p.common.dao.BaseDao;
import com.eascs.p2p.common.entity.InvestorRecordEntity;
import com.eascs.p2p.common.vo.InvestRecordVO;
import com.eascs.p2p.common.vo.TransactionRecordVO;

import jodd.util.StringUtil;

@Repository
public class InvestRecordManagerImpl extends BaseDao implements IInvestRecordManager {
	@Override
	public PageResult<InvestorRecordEntity> findFontPageInvestRecordEntity(InvestRecordVO vo) {
		SqlPageQuery<InvestorRecordEntity> sqlPageQuery = new SqlPageQuery<>(InvestorRecordEntity.class,
				vo.getPageSize(), vo.getShowPage());

		StringBuilder sql = new StringBuilder();

		sql.append(
				"SELECT p2p_invest_object.annualized_yield,p2p_investor_record.create_time,p2p_investor_record.invest_amount,investor.name investorId,p2p_invest_object.deadline");
		sql.append(
				" FROM p2p_investor_record JOIN p2p_invest_object ON p2p_invest_object.id=p2p_investor_record.object_id and p2p_invest_object.id=? left join p2p_investor investor on investor.id=p2p_investor_record.investor_id");

		sqlPageQuery.addParam(vo.getId());
		sqlPageQuery.setQuerySql(sql.toString());

		PageResult<InvestorRecordEntity> page = this.findDataWithPage(sqlPageQuery);
		return page;
	}

	@Override
	public PageResult<InvestorRecordEntity> findFontPageDealRecordEntity(InvestRecordVO vo) {
		SqlPageQuery<InvestorRecordEntity> sqlPageQuery = new SqlPageQuery<>(InvestorRecordEntity.class,
				vo.getPageSize(), vo.getShowPage());

		StringBuilder sql = new StringBuilder();

		sql.append(
				"SELECT p2p_invest_object.title,p2p_invest_object.annualized_yield,p2p_investor_record.create_time,p2p_investor_record.invest_amount,p2p_invest_object.deadline ");
		sql.append(
				" FROM p2p_investor_record JOIN p2p_invest_object ON p2p_invest_object.id=p2p_investor_record.object_id");
		sql.append(" JOIN p2p_investor ON p2p_investor.id=p2p_investor_record.investor_id");
		sql.append(" and p2p_investor.id=?");

		sqlPageQuery.addParam(vo.getInvestorId());
		sqlPageQuery.setQuerySql(sql.toString());

		PageResult<InvestorRecordEntity> page = this.findDataWithPage(sqlPageQuery);
		return page;
	}

	@Override
	public boolean updateRecordStatus(String bid) {
		String sql = "update p2p_investor_record set status=0 where object_id=?";

		return this.execute(sql, bid);
	}

	@Override
	public PageResult<Object[]> findPageInvestRecordEntity(InvestRecordVO vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getShowPage());
		StringBuilder sql = new StringBuilder();

		sql.append(
				"SELECT object.title,record.create_time,record.invest_amount,record.expect_amount,investor.name,investor.mobile");
		sql.append(" FROM p2p_investor_record record left join p2p_invest_object object ON object.id=record.object_id");
		sql.append(" left join p2p_investor investor ON investor.id=record.investor_id");
		sql.append(" where 1=1");
		// 标的id
		if (StringUtil.isNotBlank(vo.getId())) {
			sql.append(" and object.id = ?");
			sqlPageQuery.addParam(vo.getId());
		}

		// 标题
		if (StringUtil.isNotBlank(vo.getTitle())) {
			sql.append(" and object.title like ?");
			sqlPageQuery.addParam("%" + vo.getTitle() + "%");
		}
		// 投资人
		if (StringUtil.isNotBlank(vo.getName())) {
			sql.append(" and investor.name like ?");
			sqlPageQuery.addParam("%" + vo.getName() + "%");

		}
		// 投资人手机号
		if (StringUtil.isNotBlank(vo.getMobile())) {

			sql.append(" and investor.mobile like ?");
			sqlPageQuery.addParam("%" + vo.getMobile() + "%");
		}
		// 创建时间
		if (StringUtil.isNotBlank(vo.getBeginCreateTime())) {

			sql.append(" and date_format(record.create_time,'%Y-%m-%d') >= ?");
			sqlPageQuery.addParam(vo.getBeginCreateTime());
		}
		if (StringUtil.isNotBlank(vo.getEndCreateTime())) {

			sql.append(" and date_format(record.create_time,'%Y-%m-%d') <= ?");
			sqlPageQuery.addParam(vo.getEndCreateTime());
		}
		sqlPageQuery.setQuerySql(sql.toString());

		PageResult<Object[]> page = this.findDataWithPage(sqlPageQuery);
		return page;
	}

	@Override
	public InvestRecordVO findInvestRecordVO(InvestRecordVO vo) {
		StringBuilder sql = new StringBuilder(
				"select count(1) as investNum ,sum(invest_amount)  as investAmount from p2p_investor_record record ");
		sql.append("  left join p2p_invest_object object ON object.id=record.object_id");
		sql.append("  left join p2p_investor investor ON investor.id=record.investor_id");
		sql.append(" where 1=1");
		List<Object> listParams = new ArrayList<Object>();
		// 标的id
		if (StringUtil.isNotBlank(vo.getId())) {
			sql.append(" and object.id = ?");
			listParams.add(vo.getId());
		}

		// 标题
		if (StringUtil.isNotBlank(vo.getTitle())) {
			sql.append(" and object.title like ?");
			listParams.add("%" + vo.getTitle() + "%");
		}
		// 投资人
		if (StringUtil.isNotBlank(vo.getName())) {
			sql.append(" and investor.name like ?");
			listParams.add("%" + vo.getName() + "%");

		}
		// 投资人手机号
		if (StringUtil.isNotBlank(vo.getMobile())) {

			sql.append(" and investor.mobile like ?");
			listParams.add("%" + vo.getMobile() + "%");
		}
		// 创建时间
		if (StringUtil.isNotBlank(vo.getBeginCreateTime())) {

			sql.append(" and date_format(record.create_time,'%Y-%m-%d') >= ?");
			listParams.add(vo.getBeginCreateTime());
		}
		if (StringUtil.isNotBlank(vo.getEndCreateTime())) {

			sql.append(" and date_format(record.create_time,'%Y-%m-%d') <= ?");
			listParams.add(vo.getEndCreateTime());
		}

		return this.findEntity(sql.toString(), InvestRecordVO.class,listParams.toArray());
	}

	@Override
	public int getInvestorNum(InvestRecordVO vo) {
		StringBuilder sql = new StringBuilder("SELECT count(1) FROM ( SELECT record.investor_id FROM p2p_investor_record record ");
		sql.append("  left join p2p_invest_object object ON object.id=record.object_id");
		sql.append("  left join p2p_investor investor ON investor.id=record.investor_id");
		sql.append("   where  record.status = 1");
		List<Object> listParams = new ArrayList<Object>();
		// 标的id
		if (StringUtil.isNotBlank(vo.getId())) {
			sql.append(" and object.id = ?");
			listParams.add(vo.getId());
		}

		// 标题
		if (StringUtil.isNotBlank(vo.getTitle())) {
			sql.append(" and object.title like ?");
			listParams.add("%" + vo.getTitle() + "%");
		}
		// 投资人
		if (StringUtil.isNotBlank(vo.getName())) {
			sql.append(" and investor.name like ?");
			listParams.add("%" + vo.getName() + "%");

		}
		// 投资人手机号
		if (StringUtil.isNotBlank(vo.getMobile())) {

			sql.append(" and investor.mobile like ?");
			listParams.add("%" + vo.getMobile() + "%");
		}
		// 创建时间
		if (StringUtil.isNotBlank(vo.getBeginCreateTime())) {

			sql.append(" and date_format(record.create_time,'%Y-%m-%d') >= ?");
			listParams.add(vo.getBeginCreateTime());
		}
		if (StringUtil.isNotBlank(vo.getEndCreateTime())) {

			sql.append(" and date_format(record.create_time,'%Y-%m-%d') <= ?");
			listParams.add(vo.getEndCreateTime());
		}
		
		sql.append(" GROUP BY record.investor_id ) t");
		return this.findInteger(sql.toString(),listParams.toArray());
	}

	@Override
	public PageResult<TransactionRecordVO> queryPageInvestRecord(InvestorRecordEntity investRecord, int objectStatus,
			int pageSize, int pageIndex) {
		String sql = "select rec.*,obj.title,obj.deadline,obj.deadline_type from p2p_investor_record as rec,p2p_invest_object as obj where rec.object_id=obj.id";
		List<Object> paramsList = new ArrayList<>();
		if (StringUtils.isNotBlank(investRecord.getObjectId())) {
			sql += " and rec.object_id=?";
			paramsList.add(investRecord.getObjectId());
		}
		if (StringUtils.isNotBlank(investRecord.getInvestorId())) {
			sql += " and rec.investor_id=?";
			paramsList.add(investRecord.getInvestorId());
		}
		if (objectStatus != -1) {
			sql += " and obj.object_status=?";
			paramsList.add(objectStatus);
		}

		Object[] params = paramsList.toArray();
		SqlPageQuery<TransactionRecordVO> pageQuery = new SqlPageQuery<TransactionRecordVO>(TransactionRecordVO.class,
				pageSize, pageIndex, sql);
		pageQuery.addParams(params);
		return this.findDataWithPage(pageQuery);
	}
}
